/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package com.aim.util;

import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */

public class DBAdapter {
	
    private final Context mCtx;	
    
    public static final String KEY_ID = "_id";
    
    public static final String KEY_VIN = "vin";
    public static final String KEY_CASE = "case";
    public static final String KEY_MAKE = "make";
    public static final String KEY_MODEL = "model";
    public static final String KEY_COLOR = "color";
    public static final String KEY_TYPE = "type";
    public static final String KEY_PLATE = "plate";
    public static final String KEY_MILEAGE = "mileage";
    public static final String KEY_YEAR = "year";
    public static final String KEY_MARK = "mark";
    public static final String KEY_NOTE = "note";
    public static final String KEY_TOW = "tow";
    public static final String KEY_TITLE = "title";
    public static final String KEY_ADDRESS = "address";
    public static final String KEY_CITY = "city";
    public static final String KEY_STATE = "state";
    public static final String KEY_ZIP = "zip";
    public static final String KEY_DATE = "date";   
//    public static final String KEY_ID = "_id";
    
    public static final String KEY_FAVORITEID = "favoriteId";
    public static final String KEY_ROUTEID = "routeId";
    public static final String KEY_ROUTENAME = "routeName";
    public static final String KEY_STOPNAME = "stopName";
    public static final String KEY_STOPID = "stopId";
    public static final String KEY_CAMPUSID = "campusId";
    public static final String KEY_DAYID = "dayId";
    public static final String KEY_NUMBER = "number";
    
    
    
    private static final String DATABASE_NAME = "AIMDB";
    private static final String LOCALCASETABLE = "localcasetable";
    private static final int DATABASE_VERSION = 1;
    private static final String TAG = "DbAdapter";
    
    
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    /**
     * Database creation sql statement
     */
    private static final String LOCALCASETABLE_CREATE =

    "create table localcasetable (_id integer primary key autoincrement," 
    + "vin text , make text , model text," +
    		"color text ,type text ,year text ,plate text, mileage text, address text, city text, " +
    		"state text, zip text, tow text, mark integer ,title text, note text, date text);";

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(LOCALCASETABLE_CREATE);

//            db.execSQL("INSERT INTO rs VALUES(NULL,8,16,9);");

            

           
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS tables");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public DBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public DBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
    	mDb.close();
        mDbHelper.close();
        
    }

    public void insertCase(String vin, String make, String model,  String color, String type, String year, String plate,String mileage, String add, String city, String state, String zip,String tow,int mark, String title,String note) {
        ContentValues values = new ContentValues();

        values.put(KEY_VIN, vin); 
        values.put(KEY_MAKE, make);   
        values.put(KEY_MODEL, model);  
        values.put(KEY_COLOR, color); 
        values.put(KEY_TYPE, type);       
        values.put(KEY_YEAR, year);  
        values.put(KEY_PLATE, plate); 
        values.put(KEY_MILEAGE, mileage); 
        values.put(KEY_ADDRESS, add); 
        values.put(KEY_CITY, city); 
        values.put(KEY_STATE, state); 
        values.put(KEY_ZIP, zip); 
        values.put(KEY_TOW, tow);
        values.put(KEY_MARK, mark);
        values.put(KEY_TITLE, title);
        values.put(KEY_NOTE, note);  
        Calendar c = Calendar.getInstance();
        int mMonth = c.get(Calendar.MONTH);
        int mDay = c.get(Calendar.DAY_OF_MONTH);
        String date = (mMonth+1)+"/"+mDay;
        values.put(KEY_DATE, date); 
        
        
        mDb.insert(
        		LOCALCASETABLE, null, values);
        }

  public boolean updateCase(int i, String vin, String make, String model,  String color, String type, String year, String plate,String mileage, String add, String city, String state, String zip,String tow,int mark, String title,String note){
  	 ContentValues values = new ContentValues();
     values.put(KEY_VIN, vin); 
     values.put(KEY_MAKE, make);   
     values.put(KEY_MODEL, model);  
     values.put(KEY_COLOR, color); 
     values.put(KEY_TYPE, type);       
     values.put(KEY_YEAR, year);  
     values.put(KEY_PLATE, plate); 
     values.put(KEY_MILEAGE, mileage); 
     values.put(KEY_ADDRESS, add); 
     values.put(KEY_CITY, city); 
     values.put(KEY_STATE, state); 
     values.put(KEY_ZIP, zip); 
     values.put(KEY_TOW, tow);
     values.put(KEY_MARK, mark);
     values.put(KEY_TITLE, title);
     values.put(KEY_NOTE, note);  
     Calendar c = Calendar.getInstance();
     int mMonth = c.get(Calendar.MONTH);
     int mDay = c.get(Calendar.DAY_OF_MONTH);
     String date = (mMonth+1)+"/"+mDay;
     values.put(KEY_DATE, date); 
       boolean a = mDb.update(LOCALCASETABLE,values, KEY_ID + "="+ i, null) > 0;
  	return	a;
  	
      }
    
    public Cursor fetchAllLocalCases(){
    
    	String a = "select * from localcasetable;";
    	Cursor c = mDb.rawQuery(a, null);
    	 if (c != null) {
             c.moveToFirst();
         }
    	return c;
    	
    }
    
    public int getLocalCaseNo() {
        
        String a = "select max(_id) from localcasetable;";
        Cursor c = mDb.rawQuery(a, null);
        if (c != null) {
            c.moveToFirst();
        }
        
        
        int b = c.getInt(0);
        c.close();
        return b;
        
   }
    
    public void deleteCase(int i) {
        
        int num = mDb.delete(LOCALCASETABLE, KEY_ID + "="+ i, null );

        
   }

  
// public int queryStopId(String sName) {
//    	
//    	Cursor mCursor = mDb.query(true, S_TABLE, null, KEY_STOPNAME + "=" + "'"+sName.toString()+"'", null, null, null, null, null);
//        if (mCursor != null) {
//            mCursor.moveToFirst();
//        }
//		int sid = mCursor.getInt(0);
//		mCursor.close();
//    	return sid;
//    }
//    public String queryStopName(int sid) {
//    	
//    	Cursor mCursor = mDb.query(true, S_TABLE, null, KEY_STOPID + "=" + sid, null, null, null, null, null);
//        if (mCursor != null) {
//            mCursor.moveToFirst();
//        }
//		String stopName = mCursor.getString(1);
//		mCursor.close();
//    	return stopName;
//    }
//    
//    public String queryStopTag(int sid) {
//    	
//    	Cursor mCursor = mDb.query(true, S_TABLE, null, KEY_STOPID + "=" + sid, null, null, null, null, null);
//        if (mCursor != null) {
//            mCursor.moveToFirst();
//        }
//		String stopTag = mCursor.getString(2);
//		mCursor.close();
//    	return stopTag;
//    }
//    public int queryCampusId(int sid){
//    	Cursor mCursor = mDb.query(true, S_TABLE, null, KEY_STOPID + "=" + sid, null, null, null, null, null);
//        if (mCursor != null) {
//            mCursor.moveToFirst();
//        }
//		int CampusId = mCursor.getInt(3);
//		mCursor.close();
//    	return CampusId;
//    }
//    
//
//    public boolean setFavorite(int i,int sid, int rid){
//    	 ContentValues args = new ContentValues();
//         args.put(KEY_STOPID, sid);
//         args.put(KEY_ROUTEID, rid);
//         boolean a = mDb.update(F_TABLE,args, KEY_FAVORITEID + "="+ i, null) > 0;
//    	return	a;
//    	
//        }
    }

